use QUANLYGIAODUC;
go

--exec DANGKYHOC_LayDanhSachLopDaDangKy 812257, 2011, 2
CREATE PROCEDURE DANGKYHOC_LayDanhSachLopDaDangKy(@maSinhVien int, @namHoc int, @hocKy int)
AS
BEGIN
	select mon.mamonhoc, mon.tenmonhoc, mon.sotinchi, l.tenlop, l.slsvdk_chinhthuc as siso, l.sisotoida, l.malop
	from dangkihoc_ketqua dkh join lop l on dkh.malop = l.malop
		join hocphanmo hp on l.mahpmo = hp.mahpmo join monhoc mon on hp.mamonhoc = mon.mamonhoc
	where dkh.masinhvien = @maSinhVien and hp.namhoc = @namHoc and hp.hocKy = @hocKy
END
GO

-- ma sinh vien, nam hoc, hoc ky
--exec DANGKYHOC_LayDanhSachLopCoTheDangKy 812257, 2011, 2
CREATE PROCEDURE DANGKYHOC_LayDanhSachLopCoTheDangKy(@maSinhVien int, @namHoc int, @hocKy int)
AS
BEGIN
	select mon.mamonhoc, mon.tenmonhoc, mon.sotinchi, l.tenlop, l.slsvdk_chinhthuc as siso, l.sisotoida, l.malop
	from lop l join hocphanmo hp on l.mahpmo = hp.mahpmo join monhoc mon on hp.mamonhoc = mon.mamonhoc
	where l.malop in (
		-- cung khoa vs sv
		select l.malop
		from (sinhvien sv join khoa k on sv.makhoa = k.makhoa), 
			(lop l join hocphanmo hp on l.mahpmo = hp.mahpmo join monhoc mon on hp.mamonhoc = mon.mamonhoc)
		where k.makhoa = mon.makhoa and sv.masinhvien = @maSinhVien
		
		intersect
		-- cung khoa hoc
		select l.malop
		from (sinhvien sv join khoahoc kh on sv.makhoahoc = kh.makhoahoc),
			(lop l join hocphanmo hp on l.mahpmo = hp.mahpmo join khoahoc kh2 on hp.makhoahoc = kh2.makhoahoc)
		where kh.makhoahoc = kh2.makhoahoc and sv.masinhvien = @maSinhVien
		
		intersect
		-- thuoc hoc ky hien tai
		select l.malop
		from lop l join hocphanmo hp on l.mahpmo = hp.mahpmo
		where hp.namhoc = @namHoc and hp.hocky = @hocKy
		
		intersect
		-- khong thuoc hoc phan sinhvien da dang ky
		select l.malop
		from lop l
		where l.mahpmo not in (
			select l2.mahpmo
			from dangkihoc_ketqua dkh_kq join lop l2 on dkh_kq.malop = l2.malop
			where dkh_kq.masinhvien = @maSinhVien
		)
	)	
END
GO
